"""
将所有的省市地区表写入数据库
"""

import pymysql
import pandas as pd

# 自定义dic
PATHDIR = {'file_path': '../file/addressum/'}


def get_excel(fileName: str):
    """

    :param filepath:
    :return:
    """
    df = pd.read_excel(PATHDIR['file_path'] + fileName, engine='openpyxl')
    return df


def input_database(fileName: str):
    """

    :param fileName: 要写入数据库的文件名，在当前为EXCEL
    :return: 无,写入数据库
    """
    # 链接数据库
    conn = pymysql.connect(host="localhost", user="root", password="000808", database="address", charset="utf8")
    # 获取链接对象，java conn
    cur = conn.cursor()
    # 拿到待插入的df
    df = get_excel(fileName)
    err = 0
    for i in df.values:
        print(
            f"Insert into address_sum value ('{i[0]}', '{i[1]}', {i[2]}, '{i[3]}', '{i[4]}', '{i[5]}', '{i[6]}', '{i[7]}', '{i[8]}','{i[9]}')"
        )
        try:
            cur.execute(f"Insert into address_sum value ('{i[0]}', '{i[1]}', {i[2]}, '{i[3]}', '{i[4]}', '{i[5]}', '{i[6]}', '{i[7]}', '{i[8]}','{i[9]}')")
        except:
            err += 1

        # 最后提交事务
        conn.commit()

    # 最后输出成功与失败次数
    print(f"成功{len(df) - err},失败{err}次")

    cur.close()
    conn.close()


if __name__ == '__main__':
    input_database('2020年最新省市区镇村级版.xlsx')
